﻿using Microsoft.Data.Sqlite;
using System.Data;
using System.Diagnostics;

namespace DbJobsApi.Extensions
{
    public static class SqliteQueryExtensions
    {
        /// <summary>
        /// Date格式无法读取！
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Conn"></param>
        /// <param name="Sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static List<T> SqliteQuery<T>(this SqliteConnection Conn, string Sql, Dictionary<string, object> Parameters = null) where T : new()
        {
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                //SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_e_sqlite3());
                //SQLitePCL.Batteries.Init();

                Conn.Open();
            }

            //OracleGlobalization info = Conn.GetSessionInfo();
            //info.DateFormat = "YYYY-MM-DD HH24:MI:SS";
            //info.TimeStampFormat = "YYYY-MM-DD HH24:MI:SSXFF";
            //info.TimeStampTZFormat = "YYYY-MM-DD HH24:MI:SSXFF TZR";
            //info.TimeZone = "Etc/GMT-8";
            //Conn.SetSessionInfo(info);

            using var cmd = Conn.CreateCommand();
            cmd.CommandText = Sql;
            if (Parameters != null)
            {
                foreach (var item in Parameters)
                {
                    cmd.Parameters.Add(new SqliteParameter(item.Key, item.Value));
                }
            }
            LogHelper.Write($"ADO.NET SQL  {Sql}");
            using var dr = cmd.ExecuteReader();
            return dr.MapToList<T>();
        }

        public static int SqliteExcute(this SqliteConnection Conn, string Sql, Dictionary<string, object> Parameters = null)
        {
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }
            using var cmd = Conn.CreateCommand();
            cmd.CommandText = Sql;
            if (Parameters != null)
            {
                foreach (var item in Parameters)
                {
                    cmd.Parameters.Add(new SqliteParameter(item.Key, item.Value));
                }
            }
            LogHelper.Write($"ADO.NET SQL  {Sql}");
            return cmd.ExecuteNonQuery();
        }
        public static string SqliteExcuteField(this SqliteConnection Conn, string Sql, Dictionary<string, object> Parameters = null)
        {
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }
            using var cmd = Conn.CreateCommand();
            cmd.CommandText = Sql;
            if (Parameters != null)
            {
                foreach (var item in Parameters)
                {
                    cmd.Parameters.Add(new SqliteParameter(item.Key, item.Value));
                }
            }

            LogHelper.Write($"ADO.NET SQL  {Sql}");
            using var dr = cmd.ExecuteReader();
            dr.Read();
            return dr[0].ToString();
        }

        public static DataTable SqliteQueryDataTable(this SqliteConnection Conn, string Sql, Dictionary<string, object> Parameters = null)
        {
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }

            using var cmd = Conn.CreateCommand();
            cmd.CommandText = Sql;
            if (Parameters != null)
            {
                foreach (var item in Parameters)
                {
                    cmd.Parameters.Add(new SqliteParameter(item.Key, item.Value));
                }
            }
            LogHelper.Write($"ADO.NET SQL  {Sql}");
            var spda = new SqliteDataAdapter(cmd);
            var dt = new DataTable();
            spda.Fill(0, 50, dt);
            return dt;
        }
        public static DataTable[] SqliteQueryDataTable(this SqliteConnection Conn, string[] Sqls, out string[] logs)
        {
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }

            var dts = new List<DataTable>();
            var result = new List<string>();

            using var trans = Conn.BeginTransaction();
            var sw = new Stopwatch();
            string sql = String.Empty;
            try
            {
                for (int i = 0; i < Sqls.Length; i++)
                {
                    sql = Sqls[i];
                    sw.Restart();
                    using var cmd = Conn.CreateCommand();
                    cmd.CommandText = sql;
                    LogHelper.Write($"ADO.NET SQL  {sql}");
                    var lower = sql.ToLower();
                    if (lower.StartsWith("select") || lower.StartsWith("with") ||
                        lower.StartsWith("show"))
                    {
                        var spda = new SqliteDataAdapter(cmd);
                        var dt = new DataTable();
                        spda.Fill(0, 50, dt);
                        dt.ExtendedProperties.Add("sql", sql);
                        dts.Add(dt);
                    }
                    else
                    {
                        cmd.ExecuteNonQuery();
                    }

                    sw.Stop();
                    result.Add($"执行SQL\n{sql}\n执行成功 耗时:{sw.ElapsedMilliseconds}毫秒");
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                result.Add($"执行SQL\n{sql}\n执行失败 {ex.Message} 耗时:{sw.ElapsedMilliseconds}毫秒");
            }
            logs = result.ToArray();
            return dts.ToArray();
        }


    }
}
